﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

namespace EasyCLRBase
{
    /// <summary>
    /// MS SQL Export Types.
    /// </summary>
    public enum ExportType
    {
        /// <summary>
        /// Export this method as Procedure.
        /// </summary>
        Procedure,
        /// <summary>
        /// Export this method as Function.
        /// </summary>
        Function
    }
    public class ClrBase : EasyCLRBase.IClrBase
    {
        private String ExtVisibleName;
        private Type UsingType;
        /// <summary>
        /// Get assembly name for CLR creation
        /// </summary>
        private String ExternalName
        {
            get
            {
                if (string.IsNullOrEmpty(this.m_ExternalName))
                {
                    this.m_ExternalName = ((this.UsingType != null) ? this.UsingType : this.GetType()).Assembly.GetName().Name;
                }
                return this.m_ExternalName;
            }
        }
        private String m_ExternalName;

        /// <summary>
        /// Get all public static methods from the class that are containing SqlProcedure and SqlFunction Attributes and remove all that are inherit SqlFunction Attribute.
        /// </summary>
        protected virtual IEnumerable<MethodInfoDetails> Methods
        {
            get
            {
                return (from item in ((this.UsingType != null) ? this.UsingType : this.GetType()).GetMethods(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Static)
                        let attr_proc = (SqlProcedureAttribute)item.GetCustomAttributes(typeof(SqlProcedureAttribute), false).FirstOrDefault()
                        let attr_func = (SqlFunctionAttribute)item.GetCustomAttributes(typeof(SqlFunctionAttribute), false).FirstOrDefault()
                        let attr_tr = (SqlTriggerAttribute)item.GetCustomAttributes(typeof(SqlTriggerAttribute), false).FirstOrDefault()
                        where (attr_func != null && Type.Equals(((Type)attr_func.TypeId).UnderlyingSystemType, typeof(SqlFunctionAttribute)) == true) || attr_proc != null || attr_tr != null
                        select new MethodInfoDetails { Info = item, FunctionAttribute = attr_func, ProcedureAttribute = attr_proc, TriggerAttribute = attr_tr });
            }
        }
        /// <summary>
        /// Print all extended procedures/functions from the class.
        /// </summary>
        /// <returns>String mit dem man die SQL Prozeduren/Functions erzeugen kann.</returns>
        public ClrExportString GetClrCreationCode()
        {
            var result = new ClrExportString();
            StringBuilder sb = new StringBuilder();
            Type t = (this.UsingType != null) ? this.UsingType : this.GetType();

            var att = (from n in t.GetCustomAttributes(false)
                       let uda = (n is SqlUserDefinedAggregateAttribute) ? (n as SqlUserDefinedAggregateAttribute) : null
                       let udt = (n is SqlUserDefinedTypeAttribute) ? (n as SqlUserDefinedTypeAttribute) : null
                       where uda != null || udt != null
                       select new { Uda = uda, Udt = udt }).FirstOrDefault();
            if (att != null)
            {
                if (att.Uda != null)
                {
                    this.ExtVisibleName = (String.IsNullOrEmpty(att.Uda.Name) == true) ? t.Name : att.Uda.Name;
                    ///Create Aggreate by given Name or Struct Name
                    sb.Append("CREATE AGGREGATE [");
                    sb.Append(this.ExtVisibleName);
                    sb.Append("]");

                    ///Get aggregate parameters
                    var minfo = t.GetMethod("Accumulate");
                    var pinfo = minfo.GetParameters();
                    sb.Append("(");
                    AddParameters(sb, pinfo);
                    if (pinfo.Count() > 0)
                    {
                        sb = sb.Remove(sb.Length - 2, 2);
                        sb.AppendLine();
                    }
                    sb.AppendLine(")");
                    ///Create return parameter
                    sb.Append("RETURNS ");
                    sb.Append(GetSqlParam(t.GetMethod("Terminate").ReturnParameter));

                    sb.Append("\r\nEXTERNAL NAME [");
                    sb.Append(this.ExternalName);
                    sb.Append("].[");
                    sb.Append(t.FullName);
                    sb.AppendLine("]\r\n");

                    result.SqlAggregate = sb.ToString();
                }
                else if (att.Udt != null)
                {
                    this.ExtVisibleName = (String.IsNullOrEmpty(att.Udt.Name) == true) ? t.Name : att.Udt.Name;
                    ///Create Aggreate by given Name or Struct Name
                    sb.Append("CREATE TYPE [");
                    sb.Append(this.ExtVisibleName);
                    sb.Append("]\r\nEXTERNAL NAME [");
                    sb.Append(this.ExternalName);
                    sb.Append("].[");
                    sb.Append(t.FullName);
                    sb.AppendLine("]");

                    result.SqlType = sb.ToString();
                }
            }
            else
            {
                foreach (var mid in this.Methods)
                {
                    var item = mid.Info;
                    var attr_func = mid.FunctionAttribute;
                    var attr_proc = mid.ProcedureAttribute;
                    var attr_tr = mid.TriggerAttribute;
                    var pinfo = this.GetParamInfo(item);
                    if (attr_func != null)
                    {
                        Boolean table = false;
                        String tableDef = String.Empty;

                        this.ExtVisibleName = (String.IsNullOrEmpty(attr_func.Name) == true) ? item.Name : attr_func.Name;
                        sb.Append("CREATE FUNCTION [");
                        sb.Append(this.ExtVisibleName);
                        sb.Append("] ");

                        table = String.IsNullOrEmpty(attr_func.TableDefinition) == false;
                        tableDef = attr_func.TableDefinition;
                        sb.Append("(");

                        AddParameters(sb, pinfo);
                        if (pinfo.Count() > 0)
                        {
                            sb = sb.Remove(sb.Length - 2, 2);
                            sb.AppendLine();
                        }
                        sb.AppendLine(")");

                        if (table == true)
                        {
                            sb.AppendLine("RETURNS TABLE (");
                            sb.AppendLine(tableDef);
                            sb.AppendLine(")");
                        }
                        else
                        {
                            sb.Append("RETURNS ");
                            sb.Append(GetSqlParam(item.ReturnParameter));
                        }
                    }
                    else if (attr_proc != null)
                    {
                        this.ExtVisibleName = (String.IsNullOrEmpty(attr_proc.Name)) ? item.Name : attr_proc.Name;
                        sb.Append("CREATE PROCEDURE [");
                        sb.Append(this.ExtVisibleName);
                        sb.Append("] ");

                        AddParameters(sb, pinfo);

                        if (pinfo.Count() > 0)
                            sb = sb.Remove(sb.Length - 2, 2);
                    }
                    else if (attr_tr != null)
                    {
                        this.ExtVisibleName = (String.IsNullOrEmpty(attr_tr.Name)) ? item.Name : attr_tr.Name;
                        sb.Append("CREATE TRIGGER [");
                        sb.Append(this.ExtVisibleName);
                        sb.Append("]\r\nON ");
                        sb.AppendLine(attr_tr.Target);
                        sb.Append(attr_tr.Event);
                    }
                    else
                        continue;

                    sb.Append("\r\nAS\r\nEXTERNAL NAME [");
                    sb.Append(this.ExternalName);
                    sb.Append("].[");
                    sb.Append(t.FullName);
                    sb.Append("].[");
                    sb.Append(item.Name);
                    sb.AppendLine("]");

                    result.SqlProcedureFunctionTrigger.Add(sb.ToString());
                    sb.Length = 0;
                }
            }
            return result;
        }
        /// <summary>
        /// Get all parameter info from the method information.
        /// </summary>
        /// <param name="info">Method Reflection Information</param>
        /// <returns></returns>
        protected virtual IEnumerable<ParameterInfo> GetParamInfo(MethodInfo info)
        {
            return (info != null) ? info.GetParameters() : new ParameterInfo[0];
        }
        /// <summary>
        /// Convert SQLType To MS SQL Type.
        /// </summary>
        /// <param name="info">Try to get Parameter Type</param>
        /// <returns>Sql Datatype.</returns>
        private static String GetSqlParam(ParameterInfo info, String ExtVisibleName = null)
        {
            String result = String.Empty;
            if (SqlTypeToDbType.ContainsKey(info.ParameterType) == true)
            {
                result = SqlTypeToDbType[info.ParameterType];
            }
            else
            {
                var udt_attr = ((SqlUserDefinedTypeAttribute[])info.ParameterType.GetCustomAttributes(typeof(SqlUserDefinedTypeAttribute), false)).FirstOrDefault();
                if (udt_attr == null)
                    throw new NotImplementedException(
                        String.Concat("Could not find User Defined Type for the External Visible Name: ",
                        ExtVisibleName, ", with parameter name: ", info.Name, " and it type: ", info.ParameterType.FullName)
                    );

                result = udt_attr.Name;
            }
            return result;
        }
        /// <summary>
        /// Not available here
        /// </summary>
        public virtual string HowTo()
        {
            throw new NotImplementedException();
        }
        #region Static Components
        /// <summary>
        /// Dictionary of the SqlType to Database Type.
        /// </summary>
        private static IDictionary<Type, String> SqlTypeToDbType = new Dictionary<Type, String> 
        { 
            { typeof(SqlBinary), "varbinary(max)" },
            { typeof(SqlInt64), "Bigint" },
            { typeof(SqlString), "nvarchar(max)" },
            { typeof(SqlDateTime), "datetime" },
            { typeof(SqlDecimal), "decimal(38, 17)" },
            { typeof(SqlDouble), "float" },
            { typeof(SqlInt32), "int" },
            { typeof(SqlMoney), "money" },
            { typeof(SqlSingle), "real" },
            { typeof(SqlInt16), "smallint" },
            { typeof(Object), "sql_variant" },
            { typeof(SqlByte), "tinyint" },
            { typeof(SqlGuid), "uniqueidentifier" },
            { typeof(SqlBoolean), "bit" },
            /// Reference Type
            { typeof(SqlBinary).MakeByRefType(), "varbinary(4000) OUT" },
            { typeof(SqlInt64).MakeByRefType(), "Bigint OUT" },
            { typeof(SqlString).MakeByRefType(), "nvarchar(4000) OUT" },
            { typeof(SqlDateTime).MakeByRefType(), "datetime OUT" },
            { typeof(SqlDecimal).MakeByRefType(), "decimal(38, 17) OUT" },
            { typeof(SqlDouble).MakeByRefType(), "float OUT" },
            { typeof(SqlInt32).MakeByRefType(), "int OUT" },
            { typeof(SqlMoney).MakeByRefType(), "money OUT" },
            { typeof(SqlSingle).MakeByRefType(), "real OUT" },
            { typeof(SqlInt16).MakeByRefType(), "smallint OUT" },
            { typeof(Object).MakeByRefType(), "sql_variant OUT" },
            { typeof(SqlByte).MakeByRefType(), "tinyint OUT" },
            { typeof(SqlGuid).MakeByRefType(), "uniqueidentifier OUT" },
            { typeof(SqlBoolean).MakeByRefType(), "bit OUT" }
        };
        /// <summary>
        /// Add parameters to the function/procedure create statement.
        /// </summary>
        /// <param name="sb">Result string that contains the main information for clr creation.</param>
        /// <param name="parameters">list of the method parameters</param>
        /// <param name="type">export type, function or procedure</param>
        private static void AddParameters(StringBuilder sb, IEnumerable<ParameterInfo> parameters)
        {
            String paramSep = "\r\n\t@";
            ///User Defined Stuff
            String UDOutputParam = null,
                   UDComment = null;
            foreach (var param in parameters)
            {
                /// Get User Defined Parameter Value
                var l = ((ClrParameterAttribute[])param.GetCustomAttributes(typeof(ClrParameterAttribute), true)).ToList();
                var clr = l.Count == 0 ? null : l.First();
                UDOutputParam = (clr != null ? clr.Result : null);
                UDComment = clr != null ? clr.Comment : null;
                if (String.IsNullOrEmpty(UDComment) == false)
                {
                    UDComment = UDComment.Replace("\r\n", "\r\n\t--");
                    if (param.Position == 0)
                        sb.Append("\r\n");
                    sb.Append("\t--@");
                    sb.Append(param.Name);
                    sb.Append(" comment:\r\n\t--");
                    sb.Append(UDComment);
                    if (param.Position > 0)
                        sb.Append("\r\n");
                }
                sb.Append(paramSep);
                sb.Append(param.Name);
                sb.Append(" ");
                sb.Append((String.IsNullOrEmpty(UDOutputParam) == false) ? UDOutputParam : GetSqlParam(param));
                sb.Append(", ");
            }
        }
        /// <summary>
        /// Get creation clr code for structs
        /// </summary>
        /// <param name="usingType"></param>
        /// <returns></returns>
        public static ClrExportString GetClrCreationCode(Type usingType)
        {
            return new ClrBase() { UsingType = usingType }.GetClrCreationCode();
        }
        /// <summary>
        /// Some of the CLR components cannot be autoscripted over CustomAttributes. This Failover method will garantee that this type will be currectly created.
        /// Only for UDA
        /// </summary>
        /// <param name="item">Failover Type.</param>
        /// <returns>CLR Export String</returns>
        public static ClrExportString GetClrFailoverBaseCode(IClrFailoverBase item)
        {
            var result = new ClrExportString();
            StringBuilder sb = new StringBuilder();
            var t = item.GetType();
            ///Create Aggreate by given Name or Struct Name
            sb.Append("CREATE AGGREGATE [");
            if (String.IsNullOrEmpty(item.Name()) == true)
                sb.Append(t.Name);
            else
                sb.Append(item.Name());
            sb.Append("]");

            ///Get aggregate parameters
            var minfo = t.GetMethod("Accumulate");
            var pinfo = minfo.GetParameters();
            sb.Append("(");
            AddParameters(sb, pinfo);
            if (pinfo.Count() > 0)
            {
                sb = sb.Remove(sb.Length - 2, 2);
                sb.AppendLine();
            }
            sb.AppendLine(")");
            ///Create return parameter
            sb.Append("RETURNS ");
            sb.Append(GetSqlParam(t.GetMethod("Terminate").ReturnParameter));

            sb.Append("\r\nEXTERNAL NAME [");
            sb.Append(t.Assembly.GetName().Name);
            sb.Append("].[");
            sb.Append(t.FullName);
            sb.AppendLine("]\r\n");

            result.SqlAggregate = sb.ToString();
            return result;
        }
        #endregion
    }
}
